package com.oceansoft.mobile.biz.admin.service;

import static com.oceansoft.mobile.common.Constant.DB_OPER_ONE_SUCC;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.oceansoft.mobile.biz.admin.entity.BasesiteArticlePic;
import com.oceansoft.mobile.biz.admin.entity.NewsArticle;
import com.oceansoft.mobile.biz.api.entity.News;
import com.oceansoft.mobile.common.base.entity.Pager;
import com.oceansoft.mobile.common.base.entity.Query;
import com.oceansoft.mobile.common.base.entity.Result;
import com.oceansoft.mobile.common.dao.ICommonDao;

@Service
public class NewsServiceImpl implements INewsService {
	@Resource
	private ICommonDao commonDao;

	@Override
	public Pager<NewsArticle> loadNewsByPage(long merchantId, Query query) {
		String page_sql = String.format(
				"SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid FROM news t WHERE T.MER_ID =? %s",
				Query.getPageSQL("T", query));
		String count_sql = String.format(
				"SELECT COUNT(*) FROM news T WHERE T.MER_ID = ? %s",
				Query.getCountSQL("T", query));
		List<NewsArticle> list = new ArrayList<NewsArticle>();
		list = commonDao.loadList(page_sql, NewsArticle.getFullNewsMapper(),
				merchantId);
		int total = commonDao.getCount(count_sql, merchantId);
		return new Pager<NewsArticle>(total, query.getPage(), list);
	}

	public NewsArticle loadNewsById(long sid) {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid FROM news T WHERE T.SID = ?";
		NewsArticle art = commonDao.loadOne(sql,
				NewsArticle.getFullNewsMapper(), sid);
		return art;
	}

	public List<NewsArticle> loadNewsTime(long sid) {
		// String sql =
		// "SELECT * FROM news T WHERE T.showdateTime > NOW() or T.showdateTime =null";
		String sql = "select sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news t where t.showdateTime > NOW() and t.sid = ? or t.pid = ?";
		List<NewsArticle> art = commonDao.loadList(sql,
				NewsArticle.getFullNewsMapper(), sid, sid);
		return art;
	}

	public Result saveOrUpdate(NewsArticle art) {
		if (null != art) {
			art.setUpdateTime(new Date());
			// 更新产品信息表
			commonDao
					.updateOne(
							"UPDATE news SET title = ? ,content = ? ,updateTime = ?,picCover = ?,author = ?,type=? WHERE  showdateTime > NOW() and sid =?",
							art.getTitle(), art.getContent(),
							art.getUpdateTime(), art.getPicCover(),
							art.getAuthor(), art.getType(), art.getSid());
			return new Result();
		} else {
			return new Result(Result.FAILURE, "信息保存失败");
		}
	}

	public Result saveArticle(NewsArticle art) {
		Calendar day1 = Calendar.getInstance();
		day1.set(2050, 6, 5);
		art.setShowdateTime(day1.getTime());
		try {
			// 保存产品主表信息
			commonDao
					.saveOne(
							"INSERT INTO news(title,content,picCover,mer_id,status,creatTime,author,type,showdateTime,originalName,pid) VALUES"
									+ "(:title,:content,:picCover,:mer_id,:status,:creatTime,:author,:type,:showdateTime,:originalName,:pid)",
							art);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		// return new Result(Result.FAILURE, "保存失败");
		return new Result();
	}

	public Result deleteNewsById(long sid) {
		int rt = commonDao.deleteOne("DELETE FROM news WHERE SID = ?", sid);
		if (rt > 0)
			return new Result();
		return new Result(Result.FAILURE, "删除不成功");
	}

	@Override
	public List<BasesiteArticlePic> loadProductPics(long sid) {
		return commonDao.loadList(
				"SELECT * FROM SITE_PICS T WHERE T.PID = ? AND T.TOP_FLAG = 0",
				BasesiteArticlePic.getFullRowMapper(), sid);
	}

	public List<NewsArticle> loadArticles0Pre3Days() {
		return commonDao
				.loadList(
						"SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news t where (date(t.showdateTime) BETWEEN DATE_SUB(CURDATE(), INTERVAL 3  DAY) and  DATE_SUB(CURDATE(), INTERVAL 1  DAY) ) ORDER BY t.showdateTime desc",
						NewsArticle.getFullNewsMapper());
	}

	public NewsArticle loadFirstNews(long merchantId) {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid FROM news T WHERE T.MER_ID =? and type=1 and t.showdateTime <NOW() order by T.showdateTime desc limit 0,1";
		return commonDao.loadOne(sql, NewsArticle.getFullNewsMapper(),
				merchantId);
	}

	public List<NewsArticle> loadArticlesListByMerchantId(long merchantId) {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid FROM news T WHERE T.MER_ID =? and t.showdateTime <NOW() order by T.showdateTime desc";
		List<NewsArticle> list = null;
		try {
			list = commonDao.loadList(sql, NewsArticle.getFullNewsMapper(),
					merchantId);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}

	@Transactional
	public Result updateType(long pk, String prop, String val) {
		int rt = commonDao.updateColumn("news", "sid", pk, prop, val);
		if (DB_OPER_ONE_SUCC == rt)
			return new Result();
		return new Result(Result.FAILURE, "更新失败");
	}

	public List<NewsArticle> loadArticles0PreNDays(int n) {
		return commonDao
				.loadList(
						"SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news t where t.showdateTime <NOW()  and(date(t.showdateTime) BETWEEN DATE_SUB(CURDATE(), INTERVAL ?  DAY) and  DATE_SUB(CURDATE(), INTERVAL 1  DAY) ) ORDER BY t.showdateTime desc",
						NewsArticle.getFullNewsMapper(), n);
	}

	public List<NewsArticle> loadArticlesByGroupPreNDays(int n) {
		return commonDao
				.loadList(
						"SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news t where  t.showdateTime <NOW() and (date(t.showdateTime) BETWEEN DATE_SUB(CURDATE(), INTERVAL ?  DAY) and  DATE_SUB(CURDATE(), INTERVAL 1  DAY) ) ORDER BY t.showdateTime desc limit 2",
						NewsArticle.getFullNewsMapper(), n);
	}

	public Result sendTime(String sids, Date time) {
		Result result = new Result();
		String sql = "SELECT count(*) from news t where  DATE_FORMAT(showdateTime, '%Y%m%d')= DATE_FORMAT(?, '%Y%m%d')";
		int total = commonDao.getCount(sql, time);
		if (total > 0) {
			result.setDesc("当天已经发布了");
			result.setSucc(false);
			return result;
		}
		String sid[] = sids.split(",");
		List<Object[]> params = new ArrayList<Object[]>(sid.length);
		for (String id : sid) {
			params.add(new Object[] { time, id, id });
		}
		try {
			commonDao
					.updateBatch(
							"UPDATE news T SET T.showdateTime = ? where  T.showdateTime > NOW() and T.sid = ? or T.pid = ?",
							params);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public List<String> showTimes(int days) {
		List<String> newList = new ArrayList<String>();
		try {
			newList = commonDao
					.loadOneColumn(
							"select distinct date_format(showdateTime,'%Y-%m-%d') from news where showdateTime < NOW() ORDER BY showdateTime DESC LIMIT 0,?",
							String.class, days);
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}

		return newList;
	}

	@Override
	public List<NewsArticle> newsList(String date) {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid FROM news T WHERE t.showdateTime <NOW() and showdateTime like ? order by T.type ASC";
		List<NewsArticle> list = null;
		try {
			list = commonDao.loadList(sql, NewsArticle.getFullNewsMapper(),
					date + "%");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return list;
	}

	public Result addNewsByUrl(String strURL, NewsArticle newsArticle)
			throws Exception {
		URL url = new URL(strURL);
		HttpURLConnection httpConn = (HttpURLConnection) url.openConnection();
		InputStreamReader input = new InputStreamReader(
				httpConn.getInputStream(), "utf-8");
		BufferedReader bufReader = new BufferedReader(input);
		String line = "";
		StringBuilder contentBuf = new StringBuilder();
		while ((line = bufReader.readLine()) != null) {
			contentBuf.append(line);
		}
		String content = contentBuf.toString();
		content = content.replace("data-src", "src");
		// title
		int sign1 = content.indexOf("<title>");
		int sign2 = content.indexOf("</title>");

		String title = content.substring(sign1 + 7, sign2).trim();
		// content
		int con1 = content.indexOf("page-content");
		int con2 = content.indexOf("举报");
		String cont = content.trim();
		if( con2 - 280 > con1 + 20)
		{
			cont = content.substring(con1 + 20, con2 - 280).trim();
		}
		Calendar day1 = Calendar.getInstance();
		day1.set(2050, 6, 5);
		newsArticle.setAuthor("苏州公安");
		newsArticle.setTitle(title);
		newsArticle.setMer_id(3);
		newsArticle.setStatus(1);
		newsArticle.setCreatTime(new Date());
		newsArticle.setContent(cont);
		newsArticle.setShowdateTime(day1.getTime());
		int sid = commonDao
				.saveOne(
						"INSERT INTO news(title,content,picCover,mer_id,status,creatTime,author,type,originalName,showdateTime,pid) VALUES"
								+ "(:title,:content,:picCover,:mer_id,:status,:creatTime,:author,:type,:originalName,:showdateTime,:pid)",
						newsArticle);
		return new Result(sid);
	}

	@Override
	public List<NewsArticle> findTopNews() {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news where  type='0' AND showdateTime BETWEEN '2050-01-01' AND '2050-12-31'";
		List<NewsArticle> newsArticles = new ArrayList<NewsArticle>();
		newsArticles = commonDao.loadList(sql, NewsArticle.getFullNewsMapper());
		return newsArticles;
	}

	@Override
	public List<NewsArticle> findPubTopNews(int count) {
		//SELECT * FROM news T WHERE t.showdateTime <NOW() and showdateTime like ? order by T.type ASC
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news where showdateTime <NOW() AND pid = 0 order by showdateTime desc limit "+count;
		List<NewsArticle> newsArticles = new ArrayList<NewsArticle>();
		newsArticles = commonDao.loadList(sql, NewsArticle.getFullNewsMapper());
		return newsArticles;
	}

	@Override
	public List<NewsArticle> findNewsByTopNews(long pid) {
		String sql = "SELECT sid,title,author,picCover,CAST(content AS CHAR CHARACTER SET utf8) AS content,showdateTime,publishId,mer_id,STATUS,creatTime,updateTime,type,originalName,pid from news where showdateTime <NOW() AND pid = "+pid+" order by sid desc limit 2";
		List<NewsArticle> newsArticles = new ArrayList<NewsArticle>();
		newsArticles = commonDao.loadList(sql, NewsArticle.getFullNewsMapper());
		return newsArticles;
	}

	@Override
	public Pager<News> loadPushNewsByPage(Query query) {
		String page_sql = String.format("SELECT * FROM api_news t %s",Query.getPageSQL("T", query));
		String count_sql = String.format("SELECT COUNT(*) FROM api_news t %s",Query.getCountSQL("T", query));
		List<News> list = new ArrayList<News>();
		list = commonDao.loadList(page_sql, News.getNewsRowMapper());
		int total = commonDao.getCount(count_sql);
		return new Pager<News>(total, query.getPage(), list);
	}

	@Override
	public Result savePushNewsByPage(int id,NewsArticle news) {
		String sql = "SELECT * FROM api_news T WHERE T.ID = ?";
		News n = commonDao.loadOne(sql,News.getNewsRowMapper(), id);
		Calendar day1 = Calendar.getInstance();
		day1.set(2050, 6, 5);
		news.setShowdateTime(day1.getTime());
		news.setAuthor(n.getAuthor());
		news.setUpdateTime(new Date());
		news.setTitle(n.getTitle());
		news.setContent(n.getContent());
		news.setPicCover(n.getImgUrl());
		
		//拷贝新闻数据
		commonDao.saveOne("INSERT INTO news(title,content,picCover,mer_id,status,creatTime,author,type,showdateTime,originalName,pid) VALUES"
						+ "(:title,:content,:picCover,:mer_id,:status,:creatTime,:author,:type,:showdateTime,:originalName,:pid)",news);
		
		//更新推送表状态
		commonDao.updateOne("UPDATE api_news SET status = ? WHERE id = ?",News.NEWS_SEND,id);
		
		return new Result();
	}
}
